﻿using Common.Logging;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CDService
{
    class SqlHelper
    {
        private static readonly ILog Logger = LogManager.GetLogger(typeof(SqlHelper));

        public static object ExecuteScalar(string sql, string connSql)
        {
            using (SqlConnection con = new SqlConnection(connSql))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    con.Open();
                    object obj = cmd.ExecuteScalar();
                    con.Close();
                    con.Dispose();
                    return obj;
                }
            }
        }
        /// <summary>

        /// 执行查询语句，返回DataSet

        /// </summary>

        /// <param name="SQLString">查询语句</param>

        /// <param name="connSql">连接字符串</param>

        /// <returns>DataSet</returns>

        public static DataSet Query(string SQLString, string connSql)
        {
            Logger.Info(String.Format("执行sql【{0}】，数据库连接【{1}】", SQLString, connSql));
            using (SqlConnection connection = new SqlConnection(connSql))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    Logger.Error(String.Format("执行sql【{0}】出错了，错误原因：{1}", SQLString, ex.Message));
                    //throw new Exception(ex.Message);
                }
                return ds;
            }
        }

        /// <summary>

        /// 执行SQL语句，返回影响的记录数

        /// </summary>

        /// <param name="SQLString">SQL语句</param>

        /// <param name="connSql">数据库连接语句</param>

        /// <returns>影响的记录数</returns>

        public static int ExecuteSql(string SQLString, string connSql)
        {
            Logger.Info(String.Format("执行sql【{0}】，数据库连接【{1}】", SQLString, connSql));
            using (SqlConnection connection = new SqlConnection(connSql))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (SqlException ex)
                    {
                        Logger.Error(String.Format("执行sql【{0}】出错了，错误原因：{1}", SQLString, ex.Message));
                        return 0;
                    }
                }
            }
        }

        /// <summary>

        /// 执行一条计算查询结果语句，返回查询结果（object）。

        /// </summary>

        /// <param name="SQLString">计算查询结果语句</param>

        /// <returns>查询结果（object）</returns>

        public static object GetSingle(string SQLString, string connSql)
        {
            Logger.Info(String.Format("执行sql【{0}】，数据库连接【{1}】", SQLString, connSql));
            using (SqlConnection connection = new SqlConnection(connSql))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (SqlException ex)
                    {
                        Logger.Error(String.Format("执行sql【{0}】出错了，错误原因：{1}", SQLString, ex.Message));
                        return null;
                    }
                }
            }
        }


    }
}
